﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Dapper;
using DataServer.Entities;
using MySql.Data.MySqlClient;

namespace DataServer
{
    public class DapperEx 
    {
        private IDbConnection dbConnection;

        private IDbConnection DbConnection
        {
            get
            {
                dbConnection = new MySqlConnection(ConstConn.conn);

                bool isClosed = dbConnection.State == ConnectionState.Closed;
                if (isClosed) dbConnection.Open();
                return dbConnection;
            }
        }

        /// <summary>
        /// 执行增删改操作(包括批量操作)
        /// </summary>
        /// <param name="sql">sql语句(有参数参数化)</param>
        /// <param name="param">参数化值</param>
        /// <returns></returns>
        public bool Execute(string sql, object param)
        {
            bool isSuccess = false;
            if (!string.IsNullOrEmpty(sql))
            {
                IDbTransaction tran = DbConnection.BeginTransaction();
                try
                {

                    int result = DbConnection.Execute(sql, param,tran);
                    isSuccess = result > 0 ? true : false;

                    tran.Commit();
                }
                catch(Exception ex)
                {
                    isSuccess = false;
                    tran.Rollback();
                }
            }
            return isSuccess;
        }

        /// <summary>
        /// 执行存储过程操作
        /// </summary>
        /// <param name="sql">存储过程名称</param>
        /// <param name="param">参数化值</param>
        /// <returns>返回存储过程是否执行成功</returns>
        public bool ExecuteStored(string storedName, object param)
        {
            bool isSuccess = false;
            if (!string.IsNullOrEmpty(storedName))
            {
                IDbTransaction tran = DbConnection.BeginTransaction();
                try
                {
                    int result = DbConnection.Execute(storedName, param, tran, commandType: CommandType.StoredProcedure);
                    isSuccess = result > 0 ? true : false;

                    tran.Commit();
                }
                catch
                {
                    isSuccess = false;
                    tran.Rollback();
                }
            }
            return isSuccess;
        }

        /// <summary>
        /// 执行存储过程操作
        /// </summary>
        /// <param name="storedName">存储过程名称</param>
        /// <param name="param">存储过程参数</param>
        /// <returns>返回存储过程要返回的值</returns>
        public DynamicParameters ExecuteStored(string storedName, DynamicParameters param)
        {
            if (!string.IsNullOrEmpty(storedName))
            {
                IDbTransaction tran = DbConnection.BeginTransaction();
                try
                {
                    DbConnection.Execute(storedName, param,tran, commandType: CommandType.StoredProcedure);

                    tran.Commit();
                }
                catch 
                {
                    tran.Rollback();
                }
            }
            return param;
        }


        /// <summary>
        /// 查询操作
        /// </summary>
        /// <typeparam name="T">返回集合的类型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数化值</param>
        /// <returns></returns>
        public IEnumerable<T> Query<T>(string sql, object param)
        {
            IEnumerable<T> _list = default(IEnumerable<T>);
            if (!string.IsNullOrEmpty(sql))
            {
                try
                {
                    _list = DbConnection.Query<T>(sql, param);
                }
                catch { }
            }
            return _list;
        }

        /// <summary>
        /// 执行存储过程查询操作
        /// </summary>
        /// <typeparam name="T">返回集合的类型</typeparam>
        /// <param name="storedName">存储过程</param>
        /// <param name="param">参数化值</param>
        /// <returns></returns>
        public IEnumerable<T> QueryStored<T>(string storedName, object param)
        {
            IEnumerable<T> _list = default(IEnumerable<T>);
            if (!string.IsNullOrEmpty(storedName))
            {
                try
                {
                    _list = DbConnection.Query<T>(storedName, commandType: CommandType.StoredProcedure);
                }
                catch { }
            }
            return _list;
        }

        /// <summary>
        /// 查询操作返回默认第一条数据(如返回null则创建默认类型)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public T FirstOrDefault<T>(string sql, object param)
        {
            var model = default(T);
            if (!string.IsNullOrEmpty(sql))
            {
                try
                {
                    model = DbConnection.Query<T>(sql, param).FirstOrDefault();
                }
                catch { }
            }
            return model == null ? Activator.CreateInstance<T>() : model;
        }



        /// <summary>
        /// 查询一组SQL语句并返回值
        /// </summary>
        /// <typeparam name="T1">第一条语句返回集合类型</typeparam>
        /// <typeparam name="T2">第二条语句返回集合类型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数化值</param>
        /// <returns></returns>
        public Tuple<IEnumerable<T1>, IEnumerable<T2>> Query<T1, T2>(string sql, object param)
        {
            IEnumerable<T1> _item1 = null; IEnumerable<T2> _item2 = null;
            if (!string.IsNullOrEmpty(sql))
            {
                try
                {
                    using (var multi = DbConnection.QueryMultiple(sql, param))
                    {
                        _item1 = multi.Read<T1>();
                        _item2 = multi.Read<T2>();
                    }
                }
                catch { }
            }
            return Tuple.Create<IEnumerable<T1>, IEnumerable<T2>>(_item1, _item2);
        }

        /// <summary>
        /// 查询一组SQL语句并返回值
        /// </summary>
        /// <typeparam name="T1">第一条语句返回集合类型</typeparam>
        /// <typeparam name="T2">第二条语句返回集合类型</typeparam>
        /// <typeparam name="T3">第三条语句返回集合类型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数化值</param>
        /// <returns></returns>
        public Tuple<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>> Query<T1, T2, T3>(string sql, object param)
        {
            IEnumerable<T1> _item1 = null; IEnumerable<T2> _item2 = null; IEnumerable<T3> _item3 = null;
            if (!string.IsNullOrEmpty(sql))
            {
                try
                {
                    using (var multi = DbConnection.QueryMultiple(sql, param))
                    {
                        _item1 = multi.Read<T1>();
                        _item2 = multi.Read<T2>();
                        _item3 = multi.Read<T3>();
                    }
                }
                catch { }
            }
            return Tuple.Create<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>>(_item1, _item2, _item3);
        }



        public void Dispose()
        {
            if (dbConnection != null)
            {
                try
                {
                    bool isClosed = dbConnection.State == ConnectionState.Closed;
                    if (!isClosed) dbConnection.Close();
                    //dbConnection.Dispose();
                }
                catch { }
            }
        }

        #region old
        
        ///// <summary>
        ///// Insert
        ///// </summary>
        ///// <typeparam name="T"></typeparam>
        ///// <param name="ssql">insert Sql</param>
        ///// <param name="model">param</param>
        ///// <returns></returns>
        //public static int InsertT<T>(string ssql, T model)
        //{
        //    int affecgtRow = 0;
        //    using (IDbConnection conn = new MySqlConnection(ConstConn.conn))
        //    {
        //        IDbTransaction tran = conn.BeginTransaction();
        //        try
        //        {
        //            affecgtRow = conn.Execute(ssql, model,tran);
        //            tran.Commit();
        //        }
        //        catch
        //        {
        //            affecgtRow = 0;
        //            tran.Rollback();
        //        }
        //    }

        //    return affecgtRow;
        //}

        ///// <summary>
        ///// Update
        ///// </summary>
        ///// <typeparam name="T"></typeparam>
        ///// <param name="ssql">update ssql</param>
        ///// <param name="model">param</param>
        ///// <returns></returns>
        //public static int UpdateT<T>(string ssql, T model)
        //{
        //    int affecgtRow = 0;
        //    using (IDbConnection conn = new MySqlConnection(ConstConn.conn))
        //    {
        //        IDbTransaction tran = conn.BeginTransaction();
        //        try
        //        {
        //            affecgtRow = conn.Execute(ssql, model,tran);
        //            tran.Commit();
        //        }
        //        catch
        //        {
        //            affecgtRow = 0;
        //            tran.Rollback();
        //        }
        //    }

        //    return affecgtRow;
        //}

        ///// <summary>
        ///// Delete Sql
        ///// </summary>
        ///// <typeparam name="T"></typeparam>
        ///// <param name="ssql">delete ssql</param>
        ///// <param name="model">param</param>
        ///// <returns></returns>
        //public static int DeleteT<T>(string ssql, T model)
        //{
        //    int affecgtRow = 0;
        //    using (IDbConnection conn = new MySqlConnection(ConstConn.conn))
        //    {
        //        IDbTransaction tran = conn.BeginTransaction();
        //        try
        //        {
        //            affecgtRow = conn.Execute(ssql, model ,tran);
        //            tran.Commit();
        //        }
        //        catch
        //        {
        //            affecgtRow = 0;
        //            tran.Rollback();
        //        }
        //    }

        //    return affecgtRow;
        //}

        ///// <summary>
        ///// select sigle Item
        ///// </summary>
        ///// <typeparam name="T"></typeparam>
        ///// <param name="ssql">select Sql</param>
        ///// <param name="model">Param</param>
        ///// <returns></returns>
        //public T GetT<T>(string ssql, T model)
        //{
        //    T retModel = System.Activator.CreateInstance<T>();
        //    using (IDbConnection conn = new MySqlConnection(ConstConn.conn))
        //    {
        //        retModel= conn.Query<T>(ssql, model).SingleOrDefault();
        //    }
        //    return retModel;
        //}


        ///// <summary>
        ///// select List<> item
        ///// </summary>
        ///// <typeparam name="T"></typeparam>
        ///// <param name="ssql">select sql</param>
        ///// <param name="model">param</param>
        ///// <returns></returns>
        //public List<T> GetListT<T>(string ssql, T model)
        //{
        //    List<T> modelList = null;
        //    using (IDbConnection conn = new MySqlConnection(ConstConn.conn))
        //    {
        //        modelList = conn.Query<T>(ssql, model).ToList<T>();
        //    }
        //    return modelList;
        //}

//         public DataTable GetRoleInfoByUserId(string userId)
//         {
//             DataTable dt = null;

//             string sql = @"SELECT b.*,a.userid,c.name as userName  FROM userrole AS a
//                                       INNER JOIN role AS b ON a.roleid=b.id
//                                       INNER JOIN USER AS c ON c.id=a.userid
//                                       WHERE a.userid=@userid;";
//             using (IDbConnection conn = new MySqlConnection(ConstConn.conn))
//             {
//                 IDataReader reader = conn.ExecuteReader(sql, new { userid = userId });
//                 //dt = 
//             }

//             return dt;
        //         }

        #endregion 
    }
}

#region 示例
/*
  public abstract class BaseProvide
    {
        public Lazy<LiunianContext> lazyContext;

        protected LiunianContextDb
        {
            get
            {
                return lazyContext.Value;
            }
        }

    }

   public class EmployeeProvide : BaseProvide
    {
        /// <summary>
        /// 获取数据
        /// </summary>
        /// <returns></returns>
        public List<Employee> ListEmployeeByParam(int[] param)
        {
            string sql = "select * from Employee where ID in @Ids";
            return Db.Query<Employee>(sql, new { Ids = param }).ToList();
        }

        /// <summary>
        /// 获取全部数据
        /// </summary>
        /// <returns></returns>
        public List<Employee> ListEmployee()
        {
            string sql = "select * from Employee";
            return Db.Query<Employee>(sql,null).ToList();
        }

        /// <summary>
        /// 查询单个实体
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public Employee loadEmployee(int Id)
        {
            string sql = "select * from Employee where ID= @Id";
            return Db.FirstOrDefault<Employee>(sql, new { Id = Id });
        }

        /// <summary>
        /// 插入操作
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InsertEmployee(Employee model)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into Employee values (@UpdateTime,@CreateTime,@State,@LoginName,@UserName,");
            sql.Append("@RoleId,@IsUse)");
            return Db.Execute(sql.ToString(), model);
        }

        /// <summary>
        /// 更新操作
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateEmployee(Employee model)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update Employee set UpdateTime = @UpdateTime where  ID = @ID");
            return Db.Execute(sql.ToString(), model);
        }

        /// <summary>
        /// 删除操作
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public bool DeleteEmployeeById(int Id)
        {
            string sql = "delete from Employee where ID = @Id";
            return Db.Execute(sql.ToString(), new { Id = Id });
        }

        /// <summary>
        /// 执行多个语句返回多个结果
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public Tuple<int,IEnumerable<Employee>> ListGroupEmployee()
        {
            string sql = "select Count(*) from Employee select * from Employee";
            Tuple<IEnumerable<int>, IEnumerable<Employee>> queryGroup = Db.Query<int, Employee>(sql, null);
            return Tuple.Create<int, IEnumerable<Employee>>(queryGroup.Item1.FirstOrDefault(),queryGroup.Item2);
        }


    }
 */
#endregion
